import GRDB

// MARK: - 数据库管理器
final class DatabaseManager {
    static let shared = DatabaseManager()
    var dbQueue: DatabaseQueue
    
    private init() {
        do {
            let dbURL = try FileManager.default
                .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
                .appendingPathComponent("diary.db")
            
            dbQueue = try DatabaseQueue(path: dbURL.path)
            try migrateDatabase()
        } catch {
            fatalError("Database initialization failed: \(error)")
        }
    }
}

// MARK: - 版本管理
extension DatabaseManager {
    /// 数据库版本枚举（集中管理）
    private enum Version: Int {
        case migration_1 = 1
        case migration_2 = 2
        case migration_3 = 3
        case migration_4 = 4
        case migration_5 = 5
        case migration_6 = 6
        case migration_7 = 7
        case migration_8 = 8
        case migration_9 = 9
        case migration_10 = 10
        case migration_11 = 11
        case migration_12 = 12
    }
    
    /// 迁移操作结构体
    private struct Migration {
        let version: Version
        let migrate: (Database) throws -> Void
    }
    
    /// 所有迁移操作（按版本顺序排列）
    private var migrations: [Migration] {
        [
            Migration(version: .migration_1) { db in
                // 创建日记表（幂等操作）
                if try !db.tableExists("diary") {
                    try db.create(table: "diary") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("title", .text)
                        t.column("content", .text).notNull()
                        t.column("letterNum", .text)
                        t.column("userId", .text)
                        t.column("mood", .integer).notNull().defaults(to: IMoodType.NO.rawValue)
                        t.column("time", .integer).notNull().defaults(to: 0)
                        t.column("tagId", .text)
                        t.column("sound", .text)  // JSON存储
                        t.column("location", .text)  // JSON存储
                        t.column("roleType", .integer).notNull().defaults(to: IRoleType.USER.rawValue)
                        t.column("aiChat", .text)  //废弃
                        t.column("aiChat2", .text)  // JSON存储
                        t.column("roleId", .text)
                        t.column("status", .integer).notNull().defaults(to: IStatusType.NORMAL.rawValue)
                        t.column("count", .integer).notNull().defaults(to: 0)
                        t.column("closeComment", .integer).notNull().defaults(to: false)
                        t.column("commentCount", .integer).notNull().defaults(to: 0)
                        t.column("aiRule", .text)  // JSON存储
                        t.column("diaryType", .text)
                        t.column("createTime", .integer).notNull().defaults(to: 0)
                        t.column("updateTime", .integer).notNull().defaults(to: 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                    }
                }
                //评论
                if try! !db.tableExists("DiaryComment") {
                    try db.create(table: "DiaryComment") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("diaryId", .text)
                        t.column("userId", .text)
                        t.column("content", .text)
                        t.column("time", .double).notNull().defaults(to: 0)
                        // 假设RoleBean是自定义类型，这里用text存储相关信息，可按需调整
                        t.column("role", .text)
                        t.column("roleId", .text)
                        t.column("createTime", .integer).notNull().defaults(to: 0)
                        t.column("updateTime", .integer).notNull().defaults(to: 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("status", .integer).notNull().defaults(to: IStatusType.NORMAL.rawValue)
                    }
                }
                //来信
                if try! !db.tableExists("WeeklyLetter") {
                    try db.create(table: "WeeklyLetter") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("weekOfYear", .text)
                        t.column("time", .integer).notNull().defaults(to: 0)
                        t.column("acceptTime", .integer).notNull().defaults(to: 0)
                        t.column("name", .text)
                        t.column("avatar", .text)
                        t.column("roleId", .text)
                        t.column("content", .text)
                        t.column("voicePath", .text)
                        t.column("userId", .text)
                        t.column("read", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("createTime", .integer).notNull().defaults(to: 0)
                        t.column("updateTime", .integer).notNull().defaults(to: 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("status", .integer).notNull().defaults(to: IStatusType.NORMAL.rawValue)
                    }
                }
                //来电
                if try! !db.tableExists("ActiveCall") {
                    try db.create(table: "ActiveCall") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("key", .text)
                        t.column("time", .integer).notNull().defaults(to: 0)
                        t.column("content", .text)
                        t.column("userId", .text)
                        // 假设 AiRoleBean 是自定义类型，用 text 存储相关信息，可按需调整
                        t.column("aiRole", .text)
                        t.column("voiceHex", .text)
                        t.column("createTime", .integer).notNull().defaults(to: 0)
                        t.column("updateTime", .integer).notNull().defaults(to: 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("status", .integer).notNull().defaults(to: IStatusType.NORMAL.rawValue)
                    }
                }
                // 任务
                if try! !db.tableExists("task") {
                    try db.create(table: "task") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("time", .integer).notNull().defaults(to: 0)
                        t.column("name", .text)
                        t.column("timeKey", .text)
                        t.column("roleId", .text)
                        t.column("userId", .text)
                        t.column("type", .integer).notNull().defaults(to: ITaskType.CALL.rawValue)
                        t.column("execute", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("status", .integer).notNull().defaults(to: IStatusType.NORMAL.rawValue)
                    }
                }
            },
            Migration(version: .migration_2) { db in
                // 聊天
                if try! !db.tableExists("Chat") {
                    try db.create(table: "Chat") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("type", .integer).notNull().defaults(to: IChatType.USER.rawValue)
                        t.column("role", .text)
                        t.column("content", .text)
                        t.column("time", .integer).notNull().defaults(to: 0)
                        t.column("aiUserId", .text)
                        t.column("userId", .text)
                        t.column("tokens", .integer).notNull().defaults(to: 0)
                        t.column("voiceHex", .text)
                        t.column("voicePath", .text)
                        t.column("createTime", .integer).notNull().defaults(to: 0)
                        t.column("updateTime", .integer).notNull().defaults(to: 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("status", .integer).notNull().defaults(to: IStatusType.NORMAL.rawValue)
                    }
                }
            },
            Migration(version: .migration_3) { db in
                // 图片
                if try! !db.tableExists("EmojiPicture") {
                    try db.create(table: "EmojiPicture") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("text", .text)
                        t.column("path", .text)
                        t.column("width", .integer).notNull().defaults(to: 0)
                        t.column("height", .integer).notNull().defaults(to: 0)
                        t.column("mimeType", .text)
                        t.column("duration", .integer).notNull().defaults(to: 0)
                        t.column("sort", .integer).notNull().defaults(to: 0)
                    }
                }
                if try! !db.columnExists("contentType", in: "Chat") {
                    try db.execute(sql: "ALTER TABLE Chat ADD COLUMN contentType INTEGER DEFAULT 0")
                }
                
                if try! !db.columnExists("extra", in: "Chat") {
                    try db.execute(sql: "ALTER TABLE Chat ADD COLUMN extra TEXT")
                }
            },
            Migration(version: .migration_4) { db in
                if try! !db.columnExists("bubbleId", in: "diary") {
                    try db.execute(sql: "ALTER TABLE diary ADD COLUMN bubbleId TEXT")
                }
            },
            Migration(version: .migration_5) { db in
                if try! !db.columnExists("read", in: "Chat") {
                    try db.execute(sql: "ALTER TABLE Chat ADD COLUMN read INTEGER DEFAULT 0")
                }
            },
            Migration(version: .migration_6) { db in
                // 记忆库
                if try! !db.tableExists("AiMemory") {
                    try db.create(table: "AiMemory") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("content", .text)
                        t.column("aiRoleId", .text)
                        t.column("userId", .text)
                        t.column("count", .integer).notNull().defaults(to: 0)
                        t.column("roleType", .integer).notNull().defaults(to: 0)
                        t.column("createTime", .integer).notNull().defaults(to: 0)
                        t.column("updateTime", .integer).notNull().defaults(to: 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("status", .integer).notNull().defaults(to: 0)
                    }
                }
                if try! !db.columnExists("contentType", in: "Chat") {
                    try db.execute(sql: "ALTER TABLE Chat ADD COLUMN contentType INTEGER DEFAULT 0")
                }
                
                if try! !db.columnExists("extra", in: "Chat") {
                    try db.execute(sql: "ALTER TABLE Chat ADD COLUMN extra TEXT")
                }
            },
            Migration(version: .migration_7) { db in
                if try! !db.columnExists("memory", in: "Chat") {
                    try db.execute(sql: "ALTER TABLE Chat ADD COLUMN memory INTEGER DEFAULT 0")
                }
                if try! !db.columnExists("memory", in: "diary") {
                    try db.execute(sql: "ALTER TABLE diary ADD COLUMN memory INTEGER DEFAULT 0")
                }
            },
            Migration(version: .migration_8) { db in
                if try! !db.columnExists("type", in: "EmojiPicture") {
                    try db.execute(sql: "ALTER TABLE EmojiPicture ADD COLUMN type INTEGER DEFAULT 2")
                }
            },
            Migration(version: .migration_9) { db in
                if try! !db.columnExists("title", in: "WeeklyLetter") {
                    try db.execute(sql: "ALTER TABLE WeeklyLetter ADD COLUMN title TEXT")
                }
            },
            Migration(version: .migration_10) { db in
                if try! !db.columnExists("extra", in: "task") {
                    try db.execute(sql: "ALTER TABLE task ADD COLUMN extra TEXT")
                }
            },
            Migration(version: .migration_11) { db in
                if try! !db.columnExists("key", in: "EmojiPicture") {
                    try db.execute(sql: "ALTER TABLE EmojiPicture ADD COLUMN key TEXT")
                }
            },
            //由于之前migration_10，失败了，导致无法创建
            Migration(version: .migration_12) { db in
                if try! !db.columnExists("extra", in: "task") {
                    try db.execute(sql: "ALTER TABLE task ADD COLUMN extra TEXT")
                }
            },
        ].sorted { $0.version.rawValue < $1.version.rawValue } // 确保升序排列
    }
    
    /// 执行数据库迁移
    private func migrateDatabase() throws {
        let currentVersion = try getCurrentVersion()
        showLog("当前日记数据库版本: \(currentVersion)")
        
        // 筛选需要执行的迁移
        let pendingMigrations = migrations.filter {
            $0.version.rawValue > currentVersion
        }
        
        guard !pendingMigrations.isEmpty else {
            showLog("Database is up to date")
            return
        }
        
        showLog("Pending migrations: \(pendingMigrations.count)")
        
        // 执行迁移队列
        for migration in pendingMigrations {
            try dbQueue.write { db in
                do {
                    showLog("Migrating to version \(migration.version.rawValue)...")
                    try migration.migrate(db)
                    try setVersion(db: db, version: migration.version.rawValue)
                    showLog("Migration to version \(migration.version.rawValue) succeeded")
                } catch {
                    showLog("Migration to version \(migration.version.rawValue) failed: \(error)")
                    throw error
                }
            }
        }
        
        showLog("Final database version: \(try getCurrentVersion())")
    }
}

// MARK: - 版本控制
extension DatabaseManager {
    /// 获取当前数据库版本
    private func getCurrentVersion() throws -> Int {
        try dbQueue.read { db in
            try Int.fetchOne(db, sql: "PRAGMA user_version") ?? 0
        }
    }
    
    /// 设置数据库版本
    private func setVersion(db: Database, version: Int) throws {
        try db.execute(sql: "PRAGMA user_version = \(version)")
    }
}

// MARK: - 数据库扩展（安全检查）
extension Database {
    /// 检查表是否存在
    func tableExists(_ tableName: String) throws -> Bool {
        try Row.fetchOne(self, sql: """
            SELECT COUNT(*) FROM sqlite_master
            WHERE type = 'table' AND name = ?
            """, arguments: [tableName])?[0] as Int? ?? 0 > 0
    }
    
    /// 检查字段是否存在（兼容方式）
    func columnExists(_ columnName: String, in tableName: String) throws -> Bool {
        guard try tableExists(tableName) else { return false }
        return try columns(in: tableName).contains { $0.name == columnName }
    }
}

// MARK: - 使用示例
/*
 // 在AppDelegate中初始化
 _ = DatabaseManager.shared
 
 // 检查当前版本
 do {
 let version = try DatabaseManager.shared.getCurrentVersion()
 showLog("Current DB version: \(version)")
 } catch {
 showLog("Version check failed: \(error)")
 }
 */
